Re: [SQL] err: select f() from i where (f()) in (select f() from x group by j);

At 03:13 +0300 on 14/08/1999, Chad Miller wrote:


> If I create a view, with
>
> > create view foo as select timestamp, sum(num) from timelines where
>(name='foo' or name='bar' or name='baz') group by timestamp;
>
> I get:
> Table    = foo
> +-----------------------------+----------------------------------+-------+
> |              Field          |              Type                | Length|
> +-----------------------------+----------------------------------+-------+
> | timestamp                   | datetime                         |     8 |
> | sum                         | int2                             |     2 |
> +-----------------------------+----------------------------------+-------+
>
> (Note the fields' naming)  Still -- I'd like a more elegant way to do this,
> than creating a view.
>
> I came up with:
>
> > select timestamp, max(sum) from timelines where (timestamp, sum) in
>(select timestamp, sum(num) from timelines where (name='foo' or name='bar'
>or name='baz') group by timestamp);
>
> ...which returns
>
> < ERROR:  attribute 'sum' not found

The way to get rid of names that would make your life hard is to put field
aliases in the CREATE VIEW:

create view foo as select timestamp as ts_col, sum(num) as sum_col
from timelines, where ....;

Anyway, it's not the problem here. The problem is that you selected from
timelines instead of from foo.

As for a more elegant way of doing the same task, I'm not entirely sure,
because I don't have the latest PostgreSQL here, but here is a general idea:

SELECT timestamp, sum( num ) as the_sum
FROM timelines
WHERE (name='foo' or name='bar' or name='baz')
GROUP BY timestamp
ORDER BY the_sum DESC
LIMIT 1;

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma